﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using Common;

using System.IO;
using System.Data.OleDb;

namespace DAL
{
    public class Fee
    {
        public Dat_Fee SelectFee(int FeeKey)
        {
            CIMDataContext db = new CIMDataContext();

            return db.Dat_Fee.Single(f => f.FeeKey == FeeKey);
        }

        //添加费用
        public bool AddFee(Dat_Fee fee)
        {
            bool Success;
            CIMDataContext db = new CIMDataContext();

            db.Dat_Fee.InsertOnSubmit(fee);
            try
            {
                db.SubmitChanges();
                Success = true;
            }
            catch (Exception ex)
            {
                Success = false;
            }
            return Success;
        }

        public void DelFee(int FeeKey)
        {
            //对应预约单号费用清0
            string DanNum = new Fee().SelectFee(FeeKey).DanNum;

            if (!string.IsNullOrEmpty(DanNum))
            {
                new News().ResetJTfee(DanNum);
            }

            CIMDataContext db = new CIMDataContext();
            Dat_Fee  fee = db.Dat_Fee.Single(f => f.FeeKey == FeeKey);
            db.Dat_Fee.DeleteOnSubmit(fee);
            db.SubmitChanges();
        }

        //
        public bool EditFee(Dat_Fee fee)
        {
            CIMDataContext db = new CIMDataContext();
            Dat_Fee Fee = db.Dat_Fee.Single(f => f.FeeKey == fee.FeeKey);
            Fee.MUserKey = fee.MUserKey;
            Fee.FeeTime = fee.FeeTime;
            Fee.TransFee = fee.TransFee;
            Fee.FoodFee = fee.FoodFee;
            Fee.OtherFee = fee.OtherFee;
            //CreateTime = DateTime.Now,
            Fee.Remark = fee.Remark;
            //UserKey = 6

            try
            {
                db.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        //条件查询
        public IQueryable<Dat_Fee> SelectFee(Hashtable QueryTable)
        {
            CIMDataContext db = new CIMDataContext();

            IQueryable<Dat_Fee> Query = null;

            if (QueryTable == null)
            {
                if ( Convert.ToInt32(HttpContext.Current.Session["UserType"]) == 4 )    //管理员显示全部
                   Query = from f in db.Dat_Fee
                            orderby f.FeeKey descending
                            select f;
                if ( Convert.ToInt32(HttpContext.Current.Session["UserType"]) == 3) //工程师显示自己
                    Query = from f in db.Dat_Fee
                            orderby f.FeeKey descending
                            where f.MUserKey == Convert.ToInt32(HttpContext.Current.Session["UserKey"])
                            select f;
            }
            else
            {
                if (Convert.ToInt32(HttpContext.Current.Session["UserType"]) == 4)    //管理员显示全部
                        Query = from f in GetConditionQuery(QueryTable)
                                orderby f.FeeKey descending
                                select f;
                if (Convert.ToInt32(HttpContext.Current.Session["UserType"]) == 3) //工程师显示自己
                    Query = from f in GetConditionQuery(QueryTable)
                            orderby f.FeeKey descending
                            where f.MUserKey == Convert.ToInt32(HttpContext.Current.Session["UserKey"])
                            select f;
            }
            return Query;
        }

        private IQueryable<Dat_Fee> GetConditionQuery(Hashtable QueryTable)
        {
            CIMDataContext db = new CIMDataContext();

            var predicate = PredicateBuilder.True<Dat_Fee>();

            if (QueryTable["MUserKey"] != null)
                predicate = predicate.And(f => f.MUserKey == Convert.ToInt32(QueryTable["MUserKey"]));
            if (QueryTable["Keyword"] != null)
                predicate = predicate.And(f => f.Remark.Contains(QueryTable["Keyword"].ToString().Trim()));
            if (QueryTable["OneDate"] != null)
            {
                try
                {
                    DateTime OneDate = Convert.ToDateTime(QueryTable["OneDate"]);
                    predicate = predicate.And(f => f.FeeTime >= OneDate);
                }
                catch (Exception ex) { }
            }
            if (QueryTable["TwoDate"] != null)
            {
                try
                {
                    DateTime TwoDate = Convert.ToDateTime(QueryTable["TwoDate"].ToString().Split(' ')[0] + " 23:59:59");
                    predicate = predicate.And(f => f.FeeTime <= TwoDate);
                }
                catch (Exception ex) { }
            }

            return db.Dat_Fee.Where(predicate);
        }

        //Export
        public static void DataToExcel(Hashtable QueryTable)
        {
            IQueryable<Dat_Fee> Query = new Fee().SelectFee(QueryTable);

            WebUtility.DelExport();

            string FileName = "~/export/" + Guid.NewGuid().ToString() + ".xls";
            string sNewFullFile = HttpContext.Current.Server.MapPath(FileName);

            try
            {
                File.Copy(HttpContext.Current.Server.MapPath("~/common/template.xls"), sNewFullFile);
            }
            catch (Exception er)
            {
                HttpContext.Current.Response.Write(er.Message);
                return;
            }

            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=True;Data Source=" + sNewFullFile + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            OleDbCommand cmd = null;

            bool bRet = false;

            try
            {
                conn.Open();
                cmd = new OleDbCommand("create table [费用信息]([姓名] Text,[日期] Text,[预约单号] Text,[交通费(元)] Text,[餐费(元)] Text,[其他费用(元)] Text,[备注] Text)", conn);
                cmd.ExecuteNonQuery();


                string strSQL = "INSERT INTO [费用信息$] ([姓名], [日期],[预约单号],[交通费(元)],[餐费(元)],[其他费用(元)],[备注]) VALUES (?,?,?,?,?,?,?)";
                cmd = new OleDbCommand(strSQL, conn);
                for (int i = 0; i < 7; i++)
                {
                    cmd.Parameters.Add(i.ToString(), OleDbType.VarChar);
                }
                foreach (Dat_Fee fee in Query)
                {
                    cmd.Parameters[0].Value = fee.MUserKey == null ? "" : new Account().GetAccountName(fee.MUserKey);
                    cmd.Parameters[1].Value = fee.FeeTime == null ? "" : fee.FeeTime.ToString("yyyy-MM-dd");
                    cmd.Parameters[2].Value = string.IsNullOrEmpty(fee.DanNum) ? "" : fee.DanNum;
                    cmd.Parameters[3].Value = fee.TransFee == null ? "" : WebUtility.DecimalToString(fee.TransFee);
                    cmd.Parameters[4].Value = fee.FoodFee == null ? "" : WebUtility.DecimalToString(fee.FoodFee);
                    cmd.Parameters[5].Value = fee.OtherFee == null ? "" : WebUtility.DecimalToString(fee.OtherFee);
                    cmd.Parameters[6].Value = string.IsNullOrEmpty(fee.Remark) ? "" : fee.Remark;

                    cmd.ExecuteNonQuery();
                }
                bRet = true;
            }
            catch (Exception ex)
            {
                HttpContext.Current.Response.Write(ex.Message);

            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
                conn.Dispose();
            }

            if (bRet)
                HttpContext.Current.Response.Redirect(FileName);
        }
    }
}
